
import excel "${path}/parlgov/parlgov_13092023.xlsx", sheet("cabinet") firstrow clear

gen cab_date=date(start_date, "YMD")
format cab_date %td
drop if cab_date < date("19930101","YMD") // I go back more in time, in order to have the previous election for observations before 2000
keep if country_name=="Austria" | country_name=="Belgium" | country_name=="Bulgaria" | country_name=="Cyprus" | ///
country_name=="Czech Republic" | country_name=="Denmark" | country_name=="Estonia" | country_name=="Finland" | country_name=="France" | ///
country_name=="Germany" | country_name=="Greece" | country_name=="Hungary" | country_name=="Iceland" | country_name=="Ireland" | country_name=="Italy" | ///
country_name=="Latvia" | country_name=="Lithuania" | country_name=="Luxembourg" | country_name=="Malta" | country_name=="Netherlands" | ///
country_name=="Norway" | country_name=="Poland" | country_name=="Portugal" | country_name=="Portugal" | country_name=="Romania" | country_name=="Slovakia" | ///
country_name=="Slovenia" | country_name=="Spain" | country_name=="Sweden" | country_name=="Switzerland" | country_name=="United Kingdom"
*gen country_num=lower(country_name)
gen country_num = country_name
sort country_num cabinet_id cab_date 

* government left-right position, the weighted average of government parties left-right position *
egen gov_tot_seat=total(seats) if cabinet_party==1, by(cabinet_id)
replace gov_tot_seat=. if gov_tot_seat==0
bysort cabinet_name: egen gov_tot_seat2=mean(gov_tot_seat)
replace gov_tot_seat=gov_tot_seat2
drop gov_tot_seat2
egen govlr1 = total(left_right * seats) if cabinet_party==1, by(cabinet_id) 
replace govlr1=. if govlr1==0
gen cab_leftright = govlr1/gov_tot_seat
bysort cabinet_name: egen cab_leftright2=mean(cab_leftright)
replace cab_leftright=cab_leftright2
drop govlr1 cab_leftright2
lab var cab_leftright "Weighted left-right position of the government"

*party system polarization, see Dalton 2008,The Quantity and the Quality of Party Systems p.906*
egen sysmleft_right=mean(left_right), by(cabinet_id) // average left-right in the system
replace sysmleft_right=. if sysmleft_right==0
bysort cabinet_id: gen seat_share=seats*100/election_seats_total
replace seat_share=. if seat_share==0
gen sysleft_right1= seat_share * (((left_right - sysmleft_right)/5)^2)
egen sysleft_right2=total(sysleft_right1), by(cabinet_id)
gen sys_polarization=sqrt(sysleft_right2)
bysort cabinet_id: egen sys_polarization2=mean(sys_polarization)
replace sys_polarization=sys_polarization2
drop sysmleft_right sysleft_right1 sysleft_right2 sys_polarization2
lab var sys_polarization "Party System Polarization"

*let's use the same for the cabinet ideological cohesiveness
egen cabmleft_right=mean(left_right) if cabinet_party==1, by(cabinet_id) // average left-right of the government
gen cableft_right1= seat_share * (((left_right - cabmleft_right)/5)^2) if cabinet_party==1
egen cableft_right2=total(cableft_right1) if cabinet_party==1, by(cabinet_id)
gen cab_polarization=sqrt(cableft_right2) if cabinet_party==1
bysort cabinet_id: egen cab_polarization2=mean(cab_polarization)
replace cab_polarization=cab_polarization2
drop cabmleft_right cableft_right1 cableft_right2 cab_polarization2
lab var cab_polarization "Cabinet Cohesiveness"

* seat share of the government gov coalition
gen gov_seatsh=gov_tot_seat*100/election_seats_total
lab var gov_seatsh "Government seat share"

keep country_num cabinet_id cab_date sys_polarization cab_polarization cab_leftright caretaker cabinet_name gov_seatsh

rename country_num country_name

gen cntrystring = "AT" if country_name=="Austria"
replace cntrystring = "BE" if country_name=="Belgium"
replace cntrystring = "BG" if country_name=="Bulgaria"
replace cntrystring = "CY" if country_name=="Cyprus"
replace cntrystring = "CZ" if country_name=="Czech Republic"
replace cntrystring = "DK" if country_name=="Denmark"
replace cntrystring = "EE" if country_name=="Estonia"
replace cntrystring = "FI" if country_name=="Finland"
replace cntrystring = "FR" if country_name=="France"
replace cntrystring = "DE" if country_name=="Germany"
replace cntrystring = "GR" if country_name=="Greece"
replace cntrystring = "HU" if country_name=="Hungary"
replace cntrystring = "IS" if country_name=="Iceland"
replace cntrystring = "IE" if country_name=="Ireland"
replace cntrystring = "IT" if country_name=="Italy"
replace cntrystring = "LV" if country_name=="Latvia"
replace cntrystring = "LT" if country_name=="Lithuania"
replace cntrystring = "LU" if country_name=="Luxembourg"
replace cntrystring = "MT" if country_name=="Malta"
replace cntrystring = "NL" if country_name=="Netherlands"
replace cntrystring = "NO" if country_name=="Norway"
replace cntrystring = "PL" if country_name=="Poland"
replace cntrystring = "PT" if country_name=="Portugal"
replace cntrystring = "RO" if country_name=="Romania"
replace cntrystring = "SK" if inlist(country_name, "Slovak Republic", "Slovakia")
replace cntrystring = "SI" if country_name=="Slovenia"
replace cntrystring = "ES" if country_name=="Spain"
replace cntrystring = "SE" if country_name=="Sweden"
replace cntrystring = "CH" if country_name=="Switzerland"
replace cntrystring = "UK" if country_name=="United Kingdom"

tab country_name if missing(cntrystring)
rename cntrystring iso2c
rename cab_date edate

* Since originally the parlgov file is for parties, I drop the duplicates to make it per governments. I could also do it with collapse but I think this is safer
unab vlist : _all
sort `vlist'
quietly by `vlist':  gen dup = cond(_N==1,0,_n)
drop if dup>1

keep iso2c edate cabinet_id cabinet_name cab_leftright cab_polarization sys_polarization gov_seatsh caretaker

save "${path}/parlgov/parlgov_cleaned.dta", replace

